import pandas as pd
import plotly.express as px
import numpy as np
import plotly.graph_objects as go
import matplotlib.pyplot as plt
#4 interactive scatter plot of average number of opiod pills by year plot using plotly
import sqlite3 as sq3
con = sq3.connect("csvs/opioid.db")
con = sq3.connect("csvs/opioid.db")
annual = pd.read_sql_query("SELECT * from annual", con)
con.close
#Use the annual dataframe
annual = annual.rename(columns={"count":"count_1"})
annual['count_1'] = annual['count_1'].astype(int)
year_06 = annual.loc[(annual.year == '2006')]
year_13 = annual.loc[(annual.year == '2013')]
avg_counts_by_year = []
n = annual.year.value_counts()
n= n.sort_index()
for i in range(9):
my_year = annual.loc[(annual.year == str(i+2006))]
print(i+2006)
avg_counts_by_year.append(my_year.count_1.sum()/(n[i]))
print(avg_counts_by_year)
years = list(annual.year.unique())
years = [int(i) for i in years]
year_count = pd.DataFrame(years, columns=["Year"])
year_count = year_count.assign( Average_count =avg_counts_by_year)
fig_2 = px.scatter(year_count, x="Year",y="Average_count")
fig_2.show()
2006 2007 2008 2009 2010 2011 2012 2013 2014 [6725.041935483871, 7429.199546485261, 7946.208901884341, 8247.82385440364, 8871.797073170732, 9259.383717158611, 9226.517487046633, 9313.991896272286, 7972.25737439222]